package database;

import sharedspace.*;

import java.io.Externalizable;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Responsible for data transition from SQL server to the server
 * @author G3
 *
 */
public class ExecuteTestControl {
	
	private ExecuteTest executeTest = null;
	private Executetestinfo executeTestInfo = null;
	private StatisticTestInformation statisticTestInformation = null;
	private TeacherReqeust teacherRequest = null;
	private List<ExecuteTest> list = null;
	private List<ApprovingGrade> aList = null;
	private ApprovingGrade approvingGrade;
	public ExecuteTestControl() {
		// TODO Auto-generated constructor stub
		
	}
	/**
	 * Generate an array list with all Executed Tests
	 * @return ArrayList<ExecuteTest> list
	 */	
	public	List<ExecuteTest> getExecutedTestList()
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM aes.executetest e;");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
		
		JDBC.selectStatmentMatrix("SELECT * FROM executetest e;", result, ExecuteTest.NUM_OF_COLUMNS,executeTestSize);
		for(int i=0;i<executeTestSize;i++)
			{
				executeTest = new ExecuteTest();
				setExecutTest(result, i);
				list.add(executeTest);
			}
				
		return list;
		
	}
	/**
	 * Generates an array list of executed exams of a specific test
	 * @param testID
	 * @return ArrayList<ExecuteTest> list
	 */
	public List<ExecuteTest> getExecuteTestListByTestID(String testID)
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM executetest e WHERE e.testID='"+ testID+"';");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
			
		JDBC.selectStatmentMatrix("SELECT * FROM executetest e WHERE e.testID='"+testID+"';", result, ExecuteTest.NUM_OF_COLUMNS,executeTestSize);
			for(int i = 0; i<executeTestSize;i++)
				{
					executeTest = new ExecuteTest();
					setExecutTest(result, i);
					list.add(executeTest);
				}
			
			
		return list;
	}
	/**
	 * Generates an array list of executed exams of a specific course
	 * @param testID
	 * @return ArrayList<ExecuteTest> list
	 */
	public List<ExecuteTest> getExecuteTestListByCourseID(String courseID,String SubjectID )
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM executetest et ");
		list = new ArrayList<ExecuteTest>();
		ArrayList<ExecuteTest> tList=new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM executetest et " , result, ExecuteTest.NUM_OF_COLUMNS, executeTestSize);
		for (int i = 0; i<executeTestSize; i++)
		{
			executeTest = new ExecuteTest();
			setExecutTest(result,i);
			list.add(executeTest);
		}
		
		List<String> stringsList = new ArrayList<String>();
		for (int i = 0; i<list.size(); i++)
			stringsList.add(list.get(i).getTest().getTestID());
		Collections.sort(stringsList);
		String ltr,str;
		int i;
		for ( i = 0; i<stringsList.size(); i++){
			ltr = stringsList.get(i).substring(2,4);
			str=stringsList.get(i).substring(0,2);
			if (ltr.endsWith(courseID)&& str.endsWith(SubjectID))
				tList.add(list.get(i));
			} 
		return tList;
	}
	/**
	 * Generates an array list of executed exams of a specific course
	 * @param testID
	 * @return ArrayList<ExecuteTest> list
	 */
	public List<ExecuteTest> getExecuteTestListByCourseID(String courseID)
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM executetest et " +
												 "WHERE et.testID LIKE '__"+courseID+"__';");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM executetest et " +
								  "WHERE et.testID LIKE '__"+courseID+"__';", result, ExecuteTest.NUM_OF_COLUMNS, executeTestSize);
		for (int i = 0; i<executeTestSize; i++)
		{
			executeTest = new ExecuteTest();
			setExecutTest(result,i);
			list.add(executeTest);
		}
		
		return list;
	}
	/**
	 * Generates an array list of executed exams of a specific course in a specifc year and semster
	 * @param testID
	 * @return ArrayList<ExecuteTest> list
	 */
	public List<ExecuteTest> getExecuteTestListByCourseID(String courseID,int year, int semester)
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM executetest et " +
												 "WHERE et.testID LIKE '__"+courseID+"__' AND et.testSemester="+semester+" AND et.testYear="+year+";");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM executetest et " +
								  "WHERE et.testID LIKE '__"+courseID+"__' AND et.testSemester="+semester+" AND et.testYear="+year+";", result, ExecuteTest.NUM_OF_COLUMNS, executeTestSize);
		for (int i = 0; i<executeTestSize; i++)
		{
			executeTest = new ExecuteTest();
			setExecutTest(result,i);
			list.add(executeTest);
		}
		
		return list;
	}
	
	/**
	 * Generates an array list of executed exams with a specific code
	 * @param testID
	 * @return ArrayList<ExecuteTest> list
	 */
	public List<ExecuteTest> getExecuteTestListByCode(String code, int year, int semester)
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM executetest et " +
												 "WHERE et.testCode='"+code+"' AND et.testSemester="+semester+" AND et.testYear="+year+";");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM executetest et " +
								  "WHERE et.testCode='"+code+"' AND et.testSemester="+semester+" AND et.testYear="+year+";",
								  result, ExecuteTest.NUM_OF_COLUMNS, executeTestSize);
		for(int i = 0; i<executeTestSize; i++)
		{
			executeTest = new ExecuteTest();
			setExecutTest(result, i);
			list.add(executeTest);
		}
		
		return list;
	}
	/**
	 * Generates an array list of executed exams of a specific teacher
	 * @param testID
	 * @return ArrayList<ExecuteTest> list
	 */
	public List<ExecuteTest> getExecuteTestListByTeacherID(String teacherID)
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) FROM executetest et " +
												 "WHERE et.executer='"+teacherID+"';");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][ExecuteTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM executetest et " +
								  "WHERE et.executer='"+teacherID+"';",
								  result, ExecuteTest.NUM_OF_COLUMNS, executeTestSize);
		for(int i = 0; i<executeTestSize; i++)
		{
			executeTest = new ExecuteTest();
			setExecutTest(result, i);
			list.add(executeTest);
		}
		
		return list;
	}
	/**
	 * Returns an entire ExecuteTest instance which its ID equals to ExecuteTest ID
	 * @param subjectID
	 * @return Subject subject
	 */
	public ExecuteTest getExecuteTestByExecuteTestID(String executeTestID)
	{
		
		
		executeTest = new ExecuteTest();
		Object[] result = new Object[ExecuteTest.NUM_OF_COLUMNS];
		JDBC.selectStatment("SELECT * FROM executetest e WHERE e.executeTestID='"+executeTestID+"';", result, ExecuteTest.NUM_OF_COLUMNS);
		setExecutTest(result);
		return executeTest;
		
	}
	/**
	 * Generates an array list of ApprovingGrade of a specific test ID
	 * @param testID
	 * @return ArrayList<ApprovingGrade> list
	 */
	public List<ApprovingGrade> getApprovedGradeByexcuteTestID(String excuteTestID)
	{
		
		aList = new ArrayList<ApprovingGrade>();
		int approveSize = JDBC.countStatment("SELECT count(*) FROM aprovinggrade ag " +
											 "WHERE ag.executeTestID='"+excuteTestID+"';");
		Object[][] result = new Object[approveSize][ApprovingGrade.NUM_OF_COLOUMS];
		JDBC.selectStatmentMatrix("SELECT * FROM aprovinggrade ag " +
								  "WHERE ag.executeTestID='"+excuteTestID+"';"
											 , result, ApprovingGrade.NUM_OF_COLOUMS, approveSize);
		for (int i = 0; i<approveSize; i++)
		{
			setApprovingGrade(result, i);
			aList.add(approvingGrade);
			
		}
		
		
		return aList;
	}
	
	public StatisticTestInformation getStaticTestInformationByExecuteTestID(String executeTestID)
	{
		statisticTestInformation = new StatisticTestInformation();
		Object[] result = new Object[StatisticTestInformation.NUM_OF_COLUMNS];
		JDBC.selectStatment("SELECT * FROM statictestinformation sti " +
							"WHERE sti.executeTestID='"+ executeTestID +"';", result, StatisticTestInformation.NUM_OF_COLUMNS);
		setStatisticTestInformation(result);
		return statisticTestInformation;
	}
	
	public Executetestinfo getExecutetestinfo(String executeTestID)
	{
		executeTestInfo = new Executetestinfo();
		Object[] result = new Object[Executetestinfo.NUM_OF_COLUMNS];
		JDBC.selectStatment("SELECT * FROM executetestinfo eti " +
							"WHERE eti.executeTestID='"+ executeTestID+ "';", result, Executetestinfo.NUM_OF_COLUMNS);
		setExecutetestInfo(result);
		return executeTestInfo;
	}
	
	public List<TeacherReqeust> getNewTeacherRequestList()
	{
		int requestSize = JDBC.countStatment("SELECT count(*) FROM teacherrequest tr " +
											 "WHERE tr.notifyRequest=0;");
		List<TeacherReqeust> requestList = new ArrayList<TeacherReqeust>();
		Object[][] result = new Object[requestSize][TeacherReqeust.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM teacherrequest tr " +
								  "WHERE tr.notifyRequest=0;", result, TeacherReqeust.NUM_OF_COLUMNS, requestSize);
		for(int i = 0; i<requestSize; i++)
		{
			teacherRequest = new TeacherReqeust();
			setTeacherReqeust(result, i);
			requestList.add(teacherRequest);
		}
		
		return requestList;
	}
	
	public TeacherReqeust getTeacherReqeust(String executeTestID)
	{
		teacherRequest = new TeacherReqeust();
		Object[] result = new Object[TeacherReqeust.NUM_OF_COLUMNS];
		JDBC.selectStatment("SELECT * FROM teacherrequest tr " +
							"WHERE tr.executeTestID='"+ executeTestID +"';", result, TeacherReqeust.NUM_OF_COLUMNS);
		setTeacherReqeust(result);
		return teacherRequest;
	}
	
	public void updateTeacherReqeust(String executeTestID,int decision, int extraTime)
	{
		if(decision == 0)
			JDBC.updateStatment("teacherrequest", "notifyRequest", "1", "executeTestID", executeTestID);
		else
			{ 
				JDBC.updateStatment("teacherrequest", "notifyRequest", "1", "executeTestID", executeTestID);
				JDBC.updateStatment("teacherrequest", "managerResponse", "1", "executeTestID", executeTestID);
				JDBC.updateStatment("executetestinfo", "extraTime", ""+extraTime, "executeTestID", executeTestID);
			}
	}
	
	public void setTeacherReqeust(Object[] result)
	{
		teacherRequest.setExecuteTestID(result[0].toString());
		teacherRequest.setTeacherRequestChange(result[1].toString());
		teacherRequest.setManageResponse(Boolean.parseBoolean(result[2].toString()));
		teacherRequest.setExtraTime(Integer.parseInt(result[3].toString()));
		teacherRequest.setNotifyFlag(Integer.parseInt(result[4].toString()));
	}
	
	public void setTeacherReqeust(Object[][] result, int row)
	{
		teacherRequest.setExecuteTestID(result[row][0].toString());
		teacherRequest.setTeacherRequestChange(result[row][1].toString());
		teacherRequest.setManageResponse(Boolean.parseBoolean(result[row][2].toString()));
		teacherRequest.setExtraTime(Integer.parseInt(result[row][3].toString()));
		teacherRequest.setNotifyFlag(Integer.parseInt(result[row][4].toString()));
	}
	
	public void setExecutetestInfo(Object[] result)
	{
		executeTestInfo.setExecuteTestID(result[0].toString());
		
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat(DateUtils.dateFormat);
			java.util.Date parseDate = dateFormat.parse(result[1].toString());
			java.sql.Timestamp timestamp = new java.sql.Timestamp(parseDate.getTime());
			executeTestInfo.setStartTime(timestamp);
			} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		executeTestInfo.setExtraTime(Integer.parseInt(result[2].toString()));
		executeTestInfo.setNumberStudentStart(Integer.parseInt(result[3].toString()));
		executeTestInfo.setNumberStudentFinish(Integer.parseInt(result[4].toString()));
				
		
	}
	
	public void setStatisticTestInformation(Object[] result)
	{
		statisticTestInformation.setExecuteTestID(result[0].toString());
		statisticTestInformation.setTestAvarage(Float.parseFloat(result[1].toString()));
		statisticTestInformation.setTestMedian(Integer.parseInt(result[2].toString()));
		int[] gradeDistribution = new int[10];
		for(int i= 0; i<10; i++)
			gradeDistribution[i]=Integer.parseInt(result[i+3].toString());
		statisticTestInformation.setGradeDistribution(gradeDistribution);
		statisticTestInformation.setCount(Integer.parseInt(result[13].toString()));
	
		
	}
	
	public void setExecutTest(Object[][] result,int rows)
	{
		executeTest.setExecuteTestID(result[rows][0].toString());
		if (result[rows][1]!=null)
			executeTest.setTest((new TestControl()).getTestByTestID(result[rows][1].toString()));
		else executeTest.setTest(null);
		executeTest.setTestCode(result[rows][2].toString());
		executeTest.setTestType(result[rows][3].toString());
		executeTest.setTestYear(Integer.parseInt(result[rows][4].toString()));
		executeTest.setTestSemster(Integer.parseInt(result[rows][5].toString()));
		if (result[rows][6] != null)
			executeTest.setWordTestPath(result[rows][6].toString());
		else executeTest.setWordTestPath(null);
		executeTest.setExecuter((new TeacherControl().getTeacherByTeacherID(result[rows][7].toString())));
		executeTest.setTestStatus(Boolean.parseBoolean(result[rows][8].toString()));
	}
	
	public void setExecutTest(Object[] result)
	{
		executeTest.setExecuteTestID(result[0].toString());
		if (result[1]!=null)
			executeTest.setTest((new TestControl()).getTestByTestID(result[1].toString()));
		else executeTest.setTest(null);
		executeTest.setTestCode(result[2].toString());
		executeTest.setTestType(result[3].toString());
		executeTest.setTestYear(Integer.parseInt(result[4].toString()));
		executeTest.setTestSemster(Integer.parseInt(result[5].toString()));
		if (result[6] != null)
			executeTest.setWordTestPath(result[6].toString());
		else executeTest.setWordTestPath(null);
		executeTest.setExecuter((new TeacherControl().getTeacherByTeacherID(result[7].toString())));
		executeTest.setTestStatus(Boolean.parseBoolean(result[8].toString()));
	}
	
	public void setApprovingGrade(Object[][] result, int row)
	{
		approvingGrade = new ApprovingGrade();
		approvingGrade.setTeacher((new TeacherControl()).getTeacherByTeacherID(result[row][0].toString()));
		approvingGrade.setExecuteTest((new ExecuteTestControl()).getExecuteTestByExecuteTestID(result[row][1].toString()));
		//approvingGrade.setStudent((new StudentControl()).getStudentByStudentID(result[row][2].toString()));
		StudentInTest s = (new StudentControl()).getStudentInTestByExecuteTestID(approvingGrade.getExecuteTest().getExecuteTestID(), result[row][2].toString());
		approvingGrade.setStudentInTest(s);
		approvingGrade.setStudentFianlGrade(Integer.parseInt(result[row][6].toString()));
		
	}
	
	
	public String AllocationExcuteExam(ExecuteTest executeTestDummy)
	{
		list = getExecutedTestList();
		if (list.size() == 0)
		{
			executeTestDummy.setExecuteTestID("01");
			JDBC.insertStatment("INSERT into executetest "+
					"values('"+executeTestDummy.getExecuteTestID()+"','"+executeTestDummy.getTest().getTestID()+
					"','"+executeTestDummy.getTestCode()+"','"+executeTestDummy.getTestType()+"',"+executeTestDummy.getTestYear()+","
					+executeTestDummy.getTestSemster()+",'"+executeTestDummy.getWordTestPath()+"','"+executeTestDummy.getExecuter().getUserID()+"',"+executeTestDummy.getTestStatus()+");");
			return ("01") ;		
		}

		Integer size =list.size();
		size++;
		String str =new String(size.toString());
		executeTestDummy.setExecuteTestID(str);
		
		JDBC.insertStatment("INSERT into executetest "+
				"values('"+executeTestDummy.getExecuteTestID()+"','"+executeTestDummy.getTest().getTestID()+
				"','"+executeTestDummy.getTestCode()+"','"+executeTestDummy.getTestType()+"',"+executeTestDummy.getTestYear()+","
				+executeTestDummy.getTestSemster()+",'"+executeTestDummy.getWordTestPath()+"','"+executeTestDummy.getExecuter().getUserID()+"',"+executeTestDummy.getTestStatus()+");");
		return str;
	}



	public String AllocationExcuteExamInfo(Executetestinfo executeTestInfo)
	{

		JDBC.insertStatment("INSERT into executetestinfo "+
				"values('"+executeTestInfo.getExecuteTestID()+"','"+executeTestInfo.getStartTime()+
				"','"+executeTestInfo.getExtraTime()+"','"+executeTestInfo.getNumberStudentStart()+"','"+executeTestInfo.getNumberStudentFinish()+"');");
		
		String Ok=AllocationExcuteExamStistic(executeTestInfo);
		if (Ok.equals("Ok"));
		return "Ok";
	}


public String AllocationExcuteExamStistic(Executetestinfo executeTestInfo)
{

		JDBC.insertStatment("INSERT into statictestinformation (executeTestID) "+
				"values('"+executeTestInfo.getExecuteTestID()+"');");

		return "Ok";
	}
	
	public List<ExecuteTest> getExecuteTestByStudentID(String studentID)
	{
		int executeTestSize = JDBC.countStatment("SELECT count(*) from studentintest sit " +
												 "WHERE sit.studentID='"+studentID+"' AND sit.testCheckedComplete=true;");
		list = new ArrayList<ExecuteTest>();
		Object[][] result = new Object[executeTestSize][1];
		JDBC.selectStatmentMatrix("SELECT executeTestID FROM studentintest " +
								  "WHERE studentID='"+studentID+"' AND testCheckedComplete=1;", result, 1, executeTestSize);
		for(int i = 0; i<executeTestSize; i++)
		{
			executeTest = getExecuteTestByExecuteTestID(result[i][0].toString());
			list.add(executeTest);
		}
		
		return list;
	}


}


